﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DBAccessLibrary.Common;
using System.Windows.Media;
using System.Data;
using QueryResource.Common;
using QueryResource.Analytic;
using DBAccessLibrary;
using DBAccessLibrary.Model;
using System.Windows;
using System.Diagnostics;
using System.Threading.Tasks;
using Microsoft.Win32;

namespace QueryResource.ViewModel
{
    public class GridViewModel : BasePropertyChange
    {
        private string _sql = string.Empty;
        private DataSet _ds;
        private Guid _selectGuid;               //查询唯一编号
        private Dictionary<Guid, bool> _canelDic;//取消记录
        private string _tableName = string.Empty;
        private string _aliasName = string.Empty;

        public GridViewModel()
        {
            SaveContent = "启动编辑";
            ExecTime = "执行时间:";
            DataCount = "当前行数:";
            LoadText = string.Empty;
            _canelDic = new Dictionary<Guid, bool>();
            ExceptToExcelCommand = new DelegateCommand(() =>
            {
                try
                {
                    DataSetToExcel(_ds);
                }
                catch (Exception ex)
                {
                    MessageBoxHelper.Show(ex.Message);
                }
            }, () =>
            {
                return !(_ds == null || _ds.Tables.Count == 0 || _ds.Tables[0].Rows.Count == 0);
            });

            ExceptToSqlCommand = new DelegateCommand(() =>
            {
                string tablename;
                string result = DBOperatorFactory.GetGrammar(SysCache.DBConnection).
                    GetDataSetToInsertSql(_sql, _ds, msg =>
                    {
                        MessageBoxResult mr = MessageBoxHelper.ShowMessage(msg, "提示", MessageBoxButton.YesNoCancel, MessageBoxImage.Information);
                        if (mr == MessageBoxResult.Yes)
                            return true;
                        if (mr == MessageBoxResult.No)
                            return false;
                        return null;
                    }, out tablename);
                if (string.IsNullOrEmpty(result))
                    return;
                WriteSqlContent(string.Format("{0}[{1}]", tablename, _ds.Tables[0].Rows.Count), result);

            }, () =>
            {
                return !(_ds == null || _ds.Tables.Count == 0 || _ds.Tables[0].Rows.Count == 0);
            });

            SaveCommand = new DelegateCommand(() =>
            {
                SubmitData();
            });

            CanelLoadDataCommand = new DelegateCommand(() =>
            {
                StopCurrentBind();
            });
        }

        #region 命令
        /// <summary>
        /// 导出成Excel
        /// </summary>
        public DelegateCommand ExceptToExcelCommand { get; set; }
        /// <summary>
        /// 导出成Inser语句
        /// </summary>
        public DelegateCommand ExceptToSqlCommand { get; set; }
        /// <summary>
        /// 提交保存
        /// </summary>
        public DelegateCommand SaveCommand { get; set; }
        /// <summary>
        /// 取消加载数据
        /// </summary>
        public DelegateCommand CanelLoadDataCommand { get; set; }
        #endregion

        #region 属性
        /// <summary>
        /// 是否处于忙的状态
        /// </summary>
        private bool _isBusy;
        public bool IsBusy
        {
            get { return _isBusy; }
            set
            {
                if (value != _isBusy)
                {
                    _isBusy = value;
                    this.NotifyPropertyChanged(o => o.IsBusy);
                }
            }
        }
        private string _loadText;
        public string LoadText
        {
            get { return _loadText; }
            set
            {
                _loadText = value;
                this.NotifyPropertyChanged(o => o.LoadText);
            }
        }
        /// <summary>
        /// 是否已成功加载完数据
        /// </summary>
        private bool _isSucessLoadData;
        public bool IsSucessLoadData
        {
            get { return _isSucessLoadData; }
            set
            {
                if (value != _isSucessLoadData)
                {
                    _isSucessLoadData = value;
                    this.NotifyPropertyChanged(o => o.IsSucessLoadData);
                }
            }
        }
        /// <summary>
        /// 是否可以编辑数据
        /// </summary>
        private bool _isAllowEdit;
        public bool IsAllowEdit
        {
            get { return _isAllowEdit; }
            set
            {
                if (value != _isAllowEdit)
                {
                    _isAllowEdit = value;
                    this.NotifyPropertyChanged(o => o.IsAllowEdit);
                }
            }
        }
        /// <summary>
        /// 是否可以取消加载数据
        /// </summary>
        private bool _isAllowCanelLoad;
        public bool IsAllowCanelLoad
        {
            get { return _isAllowCanelLoad; }
            set
            {
                if (value != _isAllowCanelLoad)
                {
                    _isAllowCanelLoad = value;
                    this.NotifyPropertyChanged(o => o.IsAllowCanelLoad);
                }
            }
        }
        /// <summary>
        /// 保存按钮名称
        /// </summary>
        private string _saveContent;
        public string SaveContent
        {
            get { return _saveContent; }
            set
            {
                if (value != _saveContent)
                {
                    _saveContent = value;
                    this.NotifyPropertyChanged(o => o.SaveContent);
                }
            }
        }
        /// <summary>
        /// 执行时间
        /// </summary>
        private string _execTime;
        public string ExecTime
        {
            get { return _execTime; }
            set
            {
                if (value != _execTime)
                {
                    _execTime = value;
                    this.NotifyPropertyChanged(o => o.ExecTime);
                }
            }
        }
        /// <summary>
        /// 数据行数
        /// </summary>
        private string _dataCount;
        public string DataCount
        {
            get { return _dataCount; }
            set
            {
                if (value != _dataCount)
                {
                    _dataCount = value;
                    this.NotifyPropertyChanged(o => o.DataCount);
                }
            }
        }
        #endregion

        #region 外界通信
        /// <summary>
        /// 日志
        /// </summary>
        public Action<string, Color> AppendLogAction
        {
            private get;
            set;
        }
        /// <summary>
        /// 将insert语句添加到 sql编辑器中
        /// </summary>
        public Action<string, string, bool> AppendSqlContent { get; set; }
       
        /// <summary>
        /// 在其他线程中更新控件
        /// </summary>
        public Action<Action> ControlInvoke { get; set; }
        /// <summary>
        /// Grid 绑定数据
        /// </summary>
        public Action<DataTable> GridBind { get; set; }
        /// <summary>
        /// Grid 清空数据
        /// </summary>
        public Action GridClear { get; set; }
        /// <summary>
        /// Grid 取消编辑状态
        /// </summary>
        public Action GridCanelEdit { get; set; }
        /// <summary>
        /// Grid  打开编辑状态
        /// </summary>
        public Action GridStartEdit { get; set; }
        /// <summary>
        /// Grid 提交修改
        /// </summary>
        public Action GridCommitEdit { get; set; }
        /// <summary>
        /// 读取数据完成
        /// </summary>
        public Action LoadDataSucess { get; set; }
        /// <summary>
        /// 是否分页读取数据
        /// </summary>
        public Func<bool> IsLoadPage { get; set; }
        /// <summary>
        /// 一次最大查询的行数
        /// </summary>
        public Func<decimal> LoadMaxCount { get; set; }
        public string EditTableName { get { return _tableName; } }
        private void WriteLog(string msg, Color errorColor)
        {
            if (AppendLogAction != null)
            {
                AppendLogAction(msg, errorColor);
            }
        }

        private void WriteSqlContent(string tableName, string content)
        {
            if (AppendSqlContent != null)
            {
                AppendSqlContent(tableName, content, false);
            }
        }
        #endregion

        #region 绑定
        /// <summary>
        /// 绑定结束 处理状态
        /// </summary>
        private void BindEnd()
        {
            this.ControlInvoke(() =>
            {
                IsBusy = false;
                ExecTime = "执行时间:0";
                DataCount = "查询行数:0";
                this.IsAllowCanelLoad = false;
                
            });
        }
        /// <summary>
        /// 直接绑定数据
        /// </summary>
        private void DataBind()
        {
            _ds = null;
            if (GridClear != null)
                GridClear();

            Stopwatch sw = Stopwatch.StartNew();
            Action sqlAction = () =>
            {
                //首先判断 行数。如果超过 10万行 则提醒
                try
                {
                    string procName;
                    List<DBParam> paramlist;
                    if (DBOperatorFactory.GetGrammar(SysCache.DBConnection).GetExecProcInfo(_sql, out procName, out paramlist))
                    {
                        Dictionary<string, object> dic;
                        object result = DBOperatorFactory.GetOperator.ExecProc(procName, paramlist, out dic);
                        if (dic != null && dic.Count > 0)
                        {
                            foreach (string key in dic.Keys)
                            {
                                WriteLog(string.Format("{0}-->{1}", key, dic[key]), Colors.Black);
                            }
                        }
                        if (result is DataSet)
                        {
                            _ds = result as DataSet;
                        }
                        else
                        {
                            WriteLog("Result->" + result, Colors.Blue);
                        }
                        return;
                    }
                    string sql = String.Format("select count(0) from ({0})", _sql);
                    object obj = DBOperatorFactory.GetOperator.ExecScalar(sql);
                    if (obj != null)
                    {
                        decimal count = Convert.ToDecimal(obj.ToString());
                        if (count > LoadMaxCount())
                        {
                            bool isReturn = false;
                            this.ControlInvoke(() =>
                            {
                                if (MessageBoxHelper.ShowMessage(String.Format("当前查询结果为{0}，可能会影响查询速度，是否继续进行?", count), "提醒", MessageBoxButton.OKCancel, MessageBoxImage.Question) == MessageBoxResult.Cancel)
                                {
                                    isReturn = true;

                                    IsBusy = false;
                                    ExecTime = "执行时间:0";
                                    DataCount = "查询行数:0";
                                }
                            });
                            if (isReturn)
                            {
                                return;
                            }
                        }
                    }
                }
                catch { }
                try
                {

                    _ds = DBOperatorFactory.GetOperator.ExecSqlToDS(_sql);
                }
                catch (Exception ex)
                {
                    BindEnd();

                    WriteLog(ex.Message, Colors.Red);
                }
            };
            Task.Factory.FromAsync(sqlAction.BeginInvoke, sqlAction.EndInvoke, _selectGuid).ContinueWith(
                t =>
                {

                    sw.Stop();
                    if (t.Exception != null)
                    {
                    }
                    else
                    {
                        if (StopBind(t.AsyncState))
                            return;
                        if (_ds != null && _ds.Tables.Count > 0)
                        {
                            this.ControlInvoke(() =>
                            {
                                ExecTime = "执行时间:" + sw.Elapsed;
                                if (_ds != null && _ds.Tables.Count > 0)
                                {
                                    DataCount = "查询行数:" + _ds.Tables[0].Rows.Count;

                                    if (StopBind(t.AsyncState))
                                        return;
                                    GridBind(_ds.Tables[0]);

                                }
                                else
                                {
                                    ExecTime = "查询行数:0";
                                    GridBind(null);
                                }
                                //通知 数据已经加载成功
                                if (LoadDataSucess != null)
                                    LoadDataSucess();
                                WriteLog(string.Format("执行成功.{0}-----耗时:{1}{0}-----行数:{2}{0}{3}{0}",
                                    Environment.NewLine,
                                    sw.Elapsed,
                                    _ds.Tables[0].Rows.Count,
                                    "*****************************华丽的分割线*******************************")
                                    , Colors.Black);
                                IsBusy = false;
                            });
                        }
                        else
                        {
                            if (IsBusy)
                            {
                                this.ControlInvoke(() =>
                                {
                                    IsBusy = false;
                                    WriteLog("命令执行成功." + Environment.NewLine, Colors.Blue);
                                });
                            }
                        }
                    }
                });
        }
        /// <summary>
        /// 分页绑定数据
        /// </summary>
        private void PageBind()
        {
            _ds = null;

            if (GridClear != null)
                GridClear();


            Task.Factory.StartNew(BeginGridBind, _selectGuid);

        }
        private void BeginGridBind(object state)
        {
            Stopwatch sw = Stopwatch.StartNew();
            //首先判断 行数。如果超过 10万行 则提醒
            try
            {
                DataSet ds;
                string procName;
                List<DBParam> paramlist;
                if (DBOperatorFactory.GetGrammar(SysCache.DBConnection).GetExecProcInfo(_sql, out procName, out paramlist))
                {
                    Dictionary<string, object> dic;
                    DataSet result = DBOperatorFactory.GetOperator.ExecProc(procName, paramlist, out dic);
                    if (dic != null && dic.Count > 0)
                    {
                        foreach (string key in dic.Keys)
                        {
                            WriteLog(string.Format("{0}-->{1}", key, dic[key]), Colors.Black);
                        }
                    }

                    ds = result as DataSet;
                    if (ds != null && ds.Tables.Count > 0)
                    {
                        DataSet ds1 = ds;

                        this.ControlInvoke(() =>
                        {
                            if (_ds == null)
                            {
                                if (StopBind(state))
                                {
                                    return;
                                }

                                _ds = ds1;
                                GridBind(_ds.Tables[0]);
                                IsBusy = false;
                            }
                            else
                            {
                                if (_ds == null)
                                {
                                    return;
                                }
                                _ds.Tables[0].Merge(ds.Tables[0]);
                            }
                            DataCount = "当前行数:" + _ds.Tables[0].Rows.Count;
                        });
                    }
                }
                else
                {
                    decimal index = 1;
                    decimal count = LoadMaxCount();
                    while (LoadMaxCount() == count)
                    {
                        string sql = DBOperatorFactory.GetGrammar(SysCache.DBConnection).GetPageingSql(
                                                                                    _sql,
                                                                                   index,
                                                                                   index + LoadMaxCount());
                        ds = DBOperatorFactory.GetOperator.ExecSqlToDS(sql);

                        if (StopBind(state))
                        {
                            count = 0;
                            return;
                        }

                        if (ds != null && ds.Tables.Count > 0)
                        {
                            DataSet ds1 = ds;

                            this.ControlInvoke(() =>
                            {
                                if (_ds == null)
                                {
                                    if (StopBind(state))
                                    {
                                        count = 0;
                                        return;
                                    }

                                    _ds = ds1;
                                    GridBind(_ds.Tables[0]);
                                    IsBusy = false;
                                }
                                else
                                {
                                    if (_ds == null)
                                    {
                                        count = 0;
                                        return;
                                    }
                                    _ds.Tables[0].Merge(ds.Tables[0]);
                                }
                                count = ds1.Tables[0].Rows.Count;
                                index += count;
                                DataCount = "当前行数:" + _ds.Tables[0].Rows.Count;
                            });
                        }
                    }
                    StopBind(state);

                }
                //结束
                //通知 数据已经加载成功
                sw.Stop();
                this.ControlInvoke(() =>
                {
                    if (LoadDataSucess != null)
                        LoadDataSucess();
                    IsAllowCanelLoad = false;
                    ExecTime = sw.Elapsed.ToString();
                    WriteLog(
                        string.Format(
                            "执行成功.{0}-----耗时:{1}{0}-----行数:{2}{0}{3}{0}",
                            Environment.NewLine,
                            sw.Elapsed.ToString(),
                            _ds == null ? 0 : _ds.Tables[0].Rows.Count,
                            "*****************************华丽的分割线*******************************")
                        , Colors.Black);

                });
            }
            catch (Exception ex)
            {
                sw.Stop();
                BindEnd();
                WriteLog(ex.Message, Colors.Red);
            }
        }

        /// <summary>
        /// 停止绑定
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        private bool StopBind(object obj)
        {
            if (obj == null) return false;
            Guid g = (Guid)obj;

            bool isStop = true;
            if (_canelDic.ContainsKey(g))
            {
                isStop = _canelDic[g];
                if (isStop)
                {
                    _canelDic.Remove(g);
                }
            }
            return isStop;
        }
        private void StopCurrentBind()
        {
            if (_canelDic.ContainsKey(_selectGuid))
            {
                _canelDic[_selectGuid] = true;
            }
            StopBind(_selectGuid);
            IsAllowCanelLoad = false;
        }
        public void LoadData(string sql)
        {
            IsBusy = true;
            _sql = sql;
            WriteLog(_sql, Colors.Black);

            StopCurrentBind();
           
            if (GridCanelEdit != null)
                GridCanelEdit();

            //是否显示保存按钮
            if (CheckSave())
            {
                IsAllowEdit = true;
                SaveContent = "启动编辑";
            }
            else
            { IsAllowEdit = false; }
      


            //加载数据
            LoadText = "正在加载数据，请稍等...";
            _selectGuid = Guid.NewGuid();
            _canelDic.Add(_selectGuid, false);
            
            
                    
            if (IsLoadPage())
            {
                IsAllowCanelLoad = true;
                PageBind();
            }
            else
            {
                IsAllowCanelLoad = false;
                DataBind();
            }
        }
        #endregion

        /// <summary>   
        /// 常用方法，列之间加\t开。   
        /// </summary>   
        /// <remarks>   
        /// using System.IO;   
        /// </remarks>
        private void DataSetToExcel(DataSet ds)
        {
            SaveFileDialog dlg = new SaveFileDialog
            {
                Filter = "Execl files (*.xls)|*.xls",
                CheckFileExists = false,
                CheckPathExists = false,
                FilterIndex = 0,
                RestoreDirectory = true,
                CreatePrompt = true,
                Title = "保存为Excel文件"
            };

            bool? showDialog = dlg.ShowDialog();
            if (showDialog != null && showDialog.Value)
            {
                ToExcel.ExportToExcel(ds, dlg.FileName, new string[] { "ROWNUMBER" });
            }
        }
        /// <summary>
        /// 提交数据
        /// </summary>
        private void SubmitData()
        {
            try
            {
                if (SaveContent == "提交修改")
                {
                    if (GridCommitEdit != null)
                        GridCommitEdit();
                    string where = string.Empty;
                    _sql = _sql.Replace(Environment.NewLine, " ").Replace("\n", " ").Replace("\r", " ");
                    int start = _sql.IndexOf(" where ", StringComparison.CurrentCultureIgnoreCase);
                    if (start > 0)
                    {
                        where = _sql.Substring(start, _sql.Length - start);
                    }
                    if (!string.IsNullOrEmpty(_aliasName))
                    {
                        where = where.Replace(_aliasName.Trim() + ".", _tableName + ".");
                    }
                    Dictionary<string, Dictionary<string, object>> result = DBOperatorFactory.GetGrammar(SysCache.DBConnection).SubmitDataChange(_ds, _tableName, where);
                    if (result.Count > 0)
                    {
                        if (MessageBoxHelper.ShowMessage("确定提交更改过的内容?", "提示", MessageBoxButton.OKCancel, MessageBoxImage.Question) == MessageBoxResult.OK)
                        {
                            foreach (string sql in result.Keys)
                            {
                                WriteLog(sql, Colors.Black);
                                List<DBParam> list = new List<DBParam>();
                                if (result[sql] != null)
                                {
                                    foreach (string key in result[sql].Keys)
                                    {
                                        list.Add(new DBParam { Name = key, Value = result[sql][key], ParamType = 1 });
                                    }
                                }

                                DBOperatorFactory.GetOperator.ExecNonQuery(sql, SysCache.DBConnection, list);
                            }
                            _ds.AcceptChanges();
                        }
                        else
                        {
                            _ds.RejectChanges();
                        }
                    }
                }
                else
                {
                    SaveContent = "提交修改";
                    if (GridStartEdit != null)
                        GridStartEdit();
                }
            }
            catch (Exception ex)
            {
                MessageBoxHelper.Show(ex.Message);
            }
        }
        /// <summary>
        /// 判断当前SQL语句是否可以启动编辑状态
        /// </summary>
        /// <returns></returns>
        private bool CheckSave()
        {
            try
            {
                if (SysCache.DBConnection.ConnectType == DBType.DB2 ||
                    SysCache.DBConnection.ConnectType == DBType.Oracle ||
                    SysCache.DBConnection.ConnectType == DBType.SqlServer)
                {
                    if (string.IsNullOrEmpty(_sql))
                        return false;
                    if (IsLoadPage())
                    {
                        //先简单判断
                        string[] objs = _sql.ToLower().Split(new string[] { " from ", " join " }, StringSplitOptions.RemoveEmptyEntries);
                        if (objs.Length > 2)
                            return false;
                        
                        //转换成简单SQL 进行处理
                        string simpleSql = string.Format("select * {0}", _sql.Substring(_sql.IndexOf("from", StringComparison.CurrentCultureIgnoreCase)));
                        if (SysCache.DBConnection.ConnectType == DBType.DB2)
                        {
                            //过滤掉DB2 下 的前 多少行 关键字 
                            int index = simpleSql.LastIndexOf("FETCH FIRST", StringComparison.CurrentCultureIgnoreCase);
                            if (index > 0)
                            {
                                simpleSql = simpleSql.Remove(index);
                            }
                        }

                        string[] array = AnalyFactory.GetAnalytic().GetTables(simpleSql);
                        if (array.Length == 1)
                        {
                            string[] vl = array[0].Split('|');
                            _tableName = vl[0].Trim();
                            _aliasName = vl[1];
                            return true;
                        }
                    }
                    return false;
                }
                _tableName = string.Empty;
                _aliasName = string.Empty;
                return false;
            }
            catch (Exception ex)
            {
                //TODO:存在内存溢出问题
                //WriteLog(ex.Message, Colors.Red);
                return false;
            }
        }
        /// <summary>
        /// 删除行
        /// </summary>
        /// <param name="row"></param>
        public void RemoveRow(DataRow row)
        {
            try
            {
                if (row == null)
                    return;
                row.Delete();
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, Colors.Red);
            }
        }
        /// <summary>
        /// 取消注册
        /// </summary>
        public void UnRegister()
        {
            if (_canelDic != null)
            {
                if (_canelDic.ContainsKey(_selectGuid))
                {
                    _canelDic[_selectGuid] = true;
                }
            }

            _ds = null;
            _canelDic = null;
            _sql = null;

            AppendLogAction = null;
            AppendSqlContent = null;
            ControlInvoke = null;
            GridBind = null;
            GridClear = null;
            GridCanelEdit = null;
            GridStartEdit = null;
            GridCommitEdit = null;
            LoadDataSucess = null;
            IsLoadPage = null;
            LoadMaxCount = null;
        }

        public void LoadCanel()
        {
            StopCurrentBind();
            BindEnd();
        }
    }
}
